# -!- coding: utf-8 -!-
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
#作者：cacho_37967865
#博客：https://blog.csdn.net/sinat_37967865
#文件：get_index_stock.py
#日期：2021-01-22
#备注：深交所指数样本股，下载excel然后存入到数据库stock_index_sample
http://www.szse.cn/market/exponent/sample/index.html
CREATE TABLE `stock_index_sample` (
  `index_date` varchar(20) COMMENT '样本股调整时间 2023-05-09',
  `index_no` varchar(20) NOT NULL COMMENT '指数代码',
  `index_name` varchar(50) NOT NULL COMMENT '指数名称',
  `stock_no` varchar(20) NOT NULL,
  `stock_name` varchar(20) NOT NULL,
  `stock_total_no` varchar(50) NOT NULL COMMENT '股票总股本',
  `stock_market_no` varchar(50) NOT NULL COMMENT '股票流通股本',
  `stock_industry` varchar(50) NOT NULL COMMENT '股票所属行业',
  `index_status` varchar(20) default '0' COMMENT '0-纳入当前，1-已调出',
  `index_type` varchar(20) default '调入' COMMENT '调入、调出',
  `stock_flag` int(2) COMMENT '计算标志', # 0-只纳入指数成交量和金额，1-还纳入指数点数
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`index_no`,`stock_no`,`index_type`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='沪深指数样本股';
创业板指数样本调整实施时间分别为每年6月和12月的第二个星期五的下一交易日
关于调整深证成指、创业板指、深证100等指数样本股的公告
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
from pacong.stock.stock_config import db,cursor,headers
from pycacho.cachobase.logger import Logger
import requests
logger = Logger("get_index_stock").get_log()

index = {
    "399006":5,  # 创业板指数 100个  【2022-05-30，2021-11-29，2021-06-01，2020-12-01】
    "399293":3   # 创业板大盘 50个
}

def get_index(page_no,index_no):
    url = 'http://www.szse.cn/api/report/ShowReport/data?SHOWTYPE=JSON&CATALOGID=1747_zs&' \
          'PAGENO={0}&ZSDM={1}'.format(page_no,index_no)
    resp = requests.get(url, headers=headers).json()
    sub_name = resp[0]['metadata']['subname']
    index_no = sub_name.split(' ')[0]
    index_name = sub_name.split(' ')[1]
    index_date = sub_name.split(' ')[2]
    index_list = resp[0]['data']
    for data in index_list:
        stock_no = data['zqdm']
        stock_name = data['zqjc']
        stock_total_no = int(data['zgb'].replace(',',''))
        stock_market_no = int(data['ltgb'].replace(',',''))
        stock_industry = data['hylb'].replace(' ','')
        stock_flag = data['nrzsjs']  # “计算标志”为0时，表示该样本的成交量、成交金额纳入指数的成交量、成交金额，但不纳入指数点位的计算；
        index_stock(index_date,index_no,index_name,stock_no,stock_name,stock_total_no,stock_market_no,stock_industry,stock_flag)

# 将指数样本股存入到数据库
def index_stock(index_date, index_no,index_name,stock_no, stock_name,stock_total_no,stock_market_no,stock_industry,stock_flag):
    sql = "insert into stock_index_sample (index_date,index_no,index_name,stock_no,stock_name,stock_total_no,stock_market_no,stock_industry,index_status,index_type,stock_flag,create_time) " \
          "values ('%s','%s','%s','%s','%s','%s','%s','%s','0','调入','%s',now());" \
          % (index_date, index_no,index_name,stock_no, stock_name,stock_total_no,stock_market_no,stock_industry,stock_flag)
    try:
        cursor.execute(sql)
    except Exception as e:
        # 发生错误时回滚
        db.rollback()
        print(str(e))
    else:
        db.commit()
        logger.info('%s股票基本信息插入成功！', index_date+index_name+stock_no+stock_name)

def main():
    for key in index:
        for i in range(1,index[key]+1):
            get_index(i, key)

def analysis():
    sql = 'select index_no,stock_no,stock_name from stock_index_sample;'
    cursor.execute(sql)
    results = cursor.fetchall()
    row1 = []
    row2 = []
    for stock in results:
        index_no=stock[0]
        if index_no== '399006':
            row1.append(stock[1])
        if index_no == '399293':
            row2.append(stock[1])
    for stock in row1:
        if stock not in row2:
            cursor.execute('select stock_no,stock_name from stock_index_sample where index_status="0" and stock_no ="%s";'%(stock))
            results = cursor.fetchall()
            print(results[0])

if __name__ == '__main__':
    main()
    #analysis()